/**
 * Historico de alteração
 *
 * Data         Autor   Descrição
 * 24/10/2010   Jerry   Criação do cabeçalho.
 *
 */

package railbee.banco.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

import org.apache.log4j.Logger;

import railbee.banco.dao.TrensDao;
import railbee.banco.dto.Trens;
import railbee.banco.dto.TrensPk;
import railbee.banco.exceptions.TrensDaoException;


public class TrensDaoImpl extends AbstractDAO implements TrensDao
{
	/** 
	 * The factory class for this DAO has two versions of the create() method - one that
takes no arguments and one that takes a Connection argument. If the Connection version
is chosen then the connection will be stored in this attribute and will be used by all
calls to this DAO, otherwise a new Connection will be allocated for each operation.
	 */
	protected java.sql.Connection userConn;
	
	private String nomeTabela;

	protected static final Logger logger = Logger.getLogger( TrensDaoImpl.class );

	/** 
	 * All finder methods in this class use this SELECT constant to build their queries
	 */
	protected final String SQL_SELECT;

	/** 
	 * Finder methods will pass this value to the JDBC setMaxRows method
	 */
	protected int maxRows;

	/** 
	 * SQL INSERT statement for this table
	 */
	protected final String SQL_INSERT;

	/** 
	 * SQL UPDATE statement for this table
	 */
	protected final String SQL_UPDATE;

	/** 
	 * SQL DELETE statement for this table
	 */
	protected final String SQL_DELETE;

	

	/** 
	 * Index of column data
	 */
	protected static final int COLUMN_DATA = 1;

	/** 
	 * Index of column hora
	 */
	protected static final int COLUMN_HORA = 2;

	/** 
	 * Index of column estacao
	 */
	protected static final int COLUMN_ESTACAO = 3;

	/** 
	 * Index of column velocidade
	 */
	protected static final int COLUMN_VELOCIDADE = 4;

	/** 
	 * Index of column pressao
	 */
	protected static final int COLUMN_PRESSAO = 5;

	/** 
	 * Index of column tensao
	 */
	protected static final int COLUMN_TENSAO = 6;

	/** 
	 * Index of column corrente
	 */
	protected static final int COLUMN_CORRENTE = 7;

	/** 
	 * Index of column posicao
	 */
	protected static final int COLUMN_POSICAO = 8;

	/** 
	 * Index of column num_passageiros
	 */
	protected static final int COLUMN_NUM_PASSAGEIROS = 9;

	/** 
	 * Index of column id_seq_trem
	 */
	protected static final int COLUMN_ID_SEQ_TREM = 10;

	/** 
	 * Number of columns
	 */
	protected static final int NUMBER_OF_COLUMNS = 10;

	/** 
	 * Index of primary-key column id_seq_trem
	 */
	protected static final int PK_COLUMN_ID_SEQ_TREM = 1;
	
	/**
	 * Method 'TrensDaoImpl'
	 * 
	 */
//	public TrensDaoImpl()
//	{
//	}

	/**
	 * Method 'TrensDaoImpl'
	 * 
	 * @param userConn
	 */
	public TrensDaoImpl(final java.sql.Connection userConn, String nomeTabela)
	{
		this.userConn = userConn;
		this.nomeTabela = "railbee.public." + nomeTabela;
		
		/** 
		 * All finder methods in this class use this SELECT constant to build their queries
		 */
		SQL_SELECT = "SELECT data, hora, estacao, velocidade, pressao, tensao, corrente, posicao, num_passageiros, id_seq_trem FROM " + getTableName() + "";

		
		/** 
		 * SQL INSERT statement for this table
		 */
		SQL_INSERT = "INSERT INTO " + getTableName() + " ( data, hora, estacao, velocidade, pressao, tensao, corrente, posicao, num_passageiros) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?)";
//, id_seq_trem 
		/** 
		 * SQL UPDATE statement for this table
		 */
		SQL_UPDATE = "UPDATE " + getTableName() + " SET data = ?, hora = ?, estacao = ?, velocidade = ?, pressao = ?, tensao = ?, corrente = ?, posicao = ?, num_passageiros = ?, id_seq_trem = ? WHERE id_seq_trem = ?";

		/** 
		 * SQL DELETE statement for this table
		 */
		SQL_DELETE = "DELETE FROM " + getTableName() + " WHERE id_seq_trem = ?";

	}
	

	/** 
	 * Inserts a new row in the trens table.
	 */
	public TrensPk insert(Trens dto) throws TrensDaoException
	{
		long t1 = System.currentTimeMillis();
		// declare variables
		final boolean isConnSupplied = (userConn != null);
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			// get the user-specified connection or get a connection from the ResourceManager
			conn = isConnSupplied ? userConn : ResourceManager.getConnection();
		
			stmt = conn.prepareStatement( SQL_INSERT );
			int index = 1;
			stmt.setDate(index++, dto.getData()==null ? null : new java.sql.Date( dto.getData().getTime() ) );
			stmt.setTime(index++, dto.getHora()==null ? null : new java.sql.Time( dto.getHora().getTime() ) );
			stmt.setString( index++, dto.getEstacao() );
			if (dto.isVelocidadeNull()) {
				stmt.setNull( index++, java.sql.Types.FLOAT );
			} else {
				stmt.setFloat( index++, dto.getVelocidade() );
			}
		
			if (dto.isPressaoNull()) {
				stmt.setNull( index++, java.sql.Types.FLOAT );
			} else {
				stmt.setFloat( index++, dto.getPressao() );
			}
		
			if (dto.isTensaoNull()) {
				stmt.setNull( index++, java.sql.Types.FLOAT );
			} else {
				stmt.setFloat( index++, dto.getTensao() );
			}
		
			if (dto.isCorrenteNull()) {
				stmt.setNull( index++, java.sql.Types.FLOAT );
			} else {
				stmt.setFloat( index++, dto.getCorrente() );
			}
		
			if (dto.isPosicaoNull()) {
				stmt.setNull( index++, java.sql.Types.FLOAT );
			} else {
				stmt.setFloat( index++, dto.getPosicao() );
			}
		
			if (dto.isNumPassageirosNull()) {
				stmt.setNull( index++, java.sql.Types.INTEGER );
			} else {
				stmt.setInt( index++, dto.getNumPassageiros() );
			}
		
			//stmt.setLong( index++, dto.getIdSeqTrem() );
			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL_INSERT + " with DTO: " + dto);
			}
		
			int rows = stmt.executeUpdate();
			long t2 = System.currentTimeMillis();
			if (logger.isDebugEnabled()) {
				logger.debug( rows + " rows affected (" + (t2-t1) + " ms)");
			}
		
			reset(dto);
			return dto.createPk();
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new TrensDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(stmt);
			if (!isConnSupplied) {
				ResourceManager.close(conn);
			}
		
		}
		
	}

	/** 
	 * Updates a single row in the trens table.
	 */
	public void update(TrensPk pk, Trens dto) throws TrensDaoException
	{
		long t1 = System.currentTimeMillis();
		// declare variables
		final boolean isConnSupplied = (userConn != null);
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try {
			// get the user-specified connection or get a connection from the ResourceManager
			conn = isConnSupplied ? userConn : ResourceManager.getConnection();
		
			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL_UPDATE + " with DTO: " + dto);
			}
		
			stmt = conn.prepareStatement( SQL_UPDATE );
			int index=1;
			stmt.setDate(index++, dto.getData()==null ? null : new java.sql.Date( dto.getData().getTime() ) );
			stmt.setTime(index++, dto.getHora()==null ? null : new java.sql.Time( dto.getHora().getTime() ) );
			stmt.setString( index++, dto.getEstacao() );
			if (dto.isVelocidadeNull()) {
				stmt.setNull( index++, java.sql.Types.FLOAT );
			} else {
				stmt.setFloat( index++, dto.getVelocidade() );
			}
		
			if (dto.isPressaoNull()) {
				stmt.setNull( index++, java.sql.Types.FLOAT );
			} else {
				stmt.setFloat( index++, dto.getPressao() );
			}
		
			if (dto.isTensaoNull()) {
				stmt.setNull( index++, java.sql.Types.FLOAT );
			} else {
				stmt.setFloat( index++, dto.getTensao() );
			}
		
			if (dto.isCorrenteNull()) {
				stmt.setNull( index++, java.sql.Types.FLOAT );
			} else {
				stmt.setFloat( index++, dto.getCorrente() );
			}
		
			if (dto.isPosicaoNull()) {
				stmt.setNull( index++, java.sql.Types.FLOAT );
			} else {
				stmt.setFloat( index++, dto.getPosicao() );
			}
		
			if (dto.isNumPassageirosNull()) {
				stmt.setNull( index++, java.sql.Types.INTEGER );
			} else {
				stmt.setInt( index++, dto.getNumPassageiros() );
			}
		
			stmt.setLong( index++, dto.getIdSeqTrem() );
			stmt.setLong( 11, pk.getIdSeqTrem() );
			int rows = stmt.executeUpdate();
			reset(dto);
			long t2 = System.currentTimeMillis();
			if (logger.isDebugEnabled()) {
				logger.debug( rows + " rows affected (" + (t2-t1) + " ms)");
			}
		
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new TrensDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(stmt);
			if (!isConnSupplied) {
				ResourceManager.close(conn);
			}
		
		}
		
	}

	/** 
	 * Deletes a single row in the trens table.
	 */
	public void delete(TrensPk pk) throws TrensDaoException
	{
		long t1 = System.currentTimeMillis();
		// declare variables
		final boolean isConnSupplied = (userConn != null);
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try {
			// get the user-specified connection or get a connection from the ResourceManager
			conn = isConnSupplied ? userConn : ResourceManager.getConnection();
		
			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL_DELETE + " with PK: " + pk);
			}
		
			stmt = conn.prepareStatement( SQL_DELETE );
			stmt.setLong( 1, pk.getIdSeqTrem() );
			int rows = stmt.executeUpdate();
			long t2 = System.currentTimeMillis();
			if (logger.isDebugEnabled()) {
				logger.debug( rows + " rows affected (" + (t2-t1) + " ms)");
			}
		
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new TrensDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(stmt);
			if (!isConnSupplied) {
				ResourceManager.close(conn);
			}
		
		}
		
	}

	/** 
	 * Returns the rows from the trens table that matches the specified primary-key value.
	 */
	public Trens findByPrimaryKey(TrensPk pk) throws TrensDaoException
	{
		return findByPrimaryKey( pk.getIdSeqTrem() );
	}

	/** 
	 * Returns all rows from the trens table that match the criteria 'id_seq_trem = :idSeqTrem'.
	 */
	public Trens findByPrimaryKey(long idSeqTrem) throws TrensDaoException
	{
		Trens ret[] = findByDynamicSelect( SQL_SELECT + " WHERE id_seq_trem = ?", new Object[] {  new Long(idSeqTrem) } );
		return ret.length==0 ? null : ret[0];
	}

	/** 
	 * Returns all rows from the trens table that match the criteria ''.
	 */
	public Trens[] findAll() throws TrensDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " ORDER BY id_seq_trem", null );
	}

	/** 
	 * Returns all rows from the trens table that match the criteria 'data = :data'.
	 */
	public Trens[] findWhereDataEquals(Date data) throws TrensDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE data = ? ORDER BY data", new Object[] { data==null ? null : new java.sql.Date( data.getTime() ) } );
	}

	/** 
	 * Returns all rows from the trens table that match the criteria 'hora = :hora'.
	 */
	public Trens[] findWhereHoraEquals(Date hora) throws TrensDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE hora = ? ORDER BY hora", new Object[] { hora==null ? null : new java.sql.Time( hora.getTime() ) } );
	}

	/** 
	 * Returns all rows from the trens table that match the criteria 'estacao = :estacao'.
	 */
	public Trens[] findWhereEstacaoEquals(String estacao) throws TrensDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE estacao = ? ORDER BY estacao", new Object[] { estacao } );
	}

	/** 
	 * Returns all rows from the trens table that match the criteria 'velocidade = :velocidade'.
	 */
	public Trens[] findWhereVelocidadeEquals(float velocidade) throws TrensDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE velocidade = ? ORDER BY velocidade", new Object[] {  new Float(velocidade) } );
	}

	/** 
	 * Returns all rows from the trens table that match the criteria 'pressao = :pressao'.
	 */
	public Trens[] findWherePressaoEquals(float pressao) throws TrensDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE pressao = ? ORDER BY pressao", new Object[] {  new Float(pressao) } );
	}

	/** 
	 * Returns all rows from the trens table that match the criteria 'tensao = :tensao'.
	 */
	public Trens[] findWhereTensaoEquals(float tensao) throws TrensDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE tensao = ? ORDER BY tensao", new Object[] {  new Float(tensao) } );
	}

	/** 
	 * Returns all rows from the trens table that match the criteria 'corrente = :corrente'.
	 */
	public Trens[] findWhereCorrenteEquals(float corrente) throws TrensDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE corrente = ? ORDER BY corrente", new Object[] {  new Float(corrente) } );
	}

	/** 
	 * Returns all rows from the trens table that match the criteria 'posicao = :posicao'.
	 */
	public Trens[] findWherePosicaoEquals(float posicao) throws TrensDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE posicao = ? ORDER BY posicao", new Object[] {  new Float(posicao) } );
	}

	/** 
	 * Returns all rows from the trens table that match the criteria 'num_passageiros = :numPassageiros'.
	 */
	public Trens[] findWhereNumPassageirosEquals(int numPassageiros) throws TrensDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE num_passageiros = ? ORDER BY num_passageiros", new Object[] {  new Integer(numPassageiros) } );
	}

	/** 
	 * Returns all rows from the trens table that match the criteria 'id_seq_trem = :idSeqTrem'.
	 */
	public Trens[] findWhereIdSeqTremEquals(long idSeqTrem) throws TrensDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE id_seq_trem = ? ORDER BY id_seq_trem", new Object[] {  new Long(idSeqTrem) } );
	}

	

	/** 
	 * Sets the value of maxRows
	 */
	public void setMaxRows(int maxRows)
	{
		this.maxRows = maxRows;
	}

	/** 
	 * Gets the value of maxRows
	 */
	public int getMaxRows()
	{
		return maxRows;
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return nomeTabela;
	}

	/** 
	 * Fetches a single row from the result set
	 */
	protected Trens fetchSingleResult(ResultSet rs) throws SQLException
	{
		if (rs.next()) {
			Trens dto = new Trens();
			populateDto( dto, rs);
			return dto;
		} else {
			return null;
		}
		
	}

	/** 
	 * Fetches multiple rows from the result set
	 */
	protected Trens[] fetchMultiResults(ResultSet rs) throws SQLException
	{
		Collection resultList = new ArrayList();
		while (rs.next()) {
			Trens dto = new Trens();
			populateDto( dto, rs);
			resultList.add( dto );
		}
		
		Trens ret[] = new Trens[ resultList.size() ];
		resultList.toArray( ret );
		return ret;
	}

	/** 
	 * Populates a DTO with data from a ResultSet
	 */
	protected void populateDto(Trens dto, ResultSet rs) throws SQLException
	{
		dto.setData( rs.getDate(COLUMN_DATA ) );
		dto.setHora( rs.getTime(COLUMN_HORA ) );
		dto.setEstacao( rs.getString( COLUMN_ESTACAO ) );
		dto.setVelocidade( rs.getFloat( COLUMN_VELOCIDADE ) );
		if (rs.wasNull()) {
			dto.setVelocidadeNull( true );
		}
		
		dto.setPressao( rs.getFloat( COLUMN_PRESSAO ) );
		if (rs.wasNull()) {
			dto.setPressaoNull( true );
		}
		
		dto.setTensao( rs.getFloat( COLUMN_TENSAO ) );
		if (rs.wasNull()) {
			dto.setTensaoNull( true );
		}
		
		dto.setCorrente( rs.getFloat( COLUMN_CORRENTE ) );
		if (rs.wasNull()) {
			dto.setCorrenteNull( true );
		}
		
		dto.setPosicao( rs.getFloat( COLUMN_POSICAO ) );
		if (rs.wasNull()) {
			dto.setPosicaoNull( true );
		}
		
		dto.setNumPassageiros( rs.getInt( COLUMN_NUM_PASSAGEIROS ) );
		if (rs.wasNull()) {
			dto.setNumPassageirosNull( true );
		}
		
		dto.setIdSeqTrem( rs.getLong( COLUMN_ID_SEQ_TREM ) );
	}

	/** 
	 * Resets the modified attributes in the DTO
	 */
	protected void reset(Trens dto)
	{
	}

	/** 
	 * Returns all rows from the trens table that match the specified arbitrary SQL statement
	 */
	public Trens[] findByDynamicSelect(String sql, Object[] sqlParams) throws TrensDaoException
	{
		// declare variables
		final boolean isConnSupplied = (userConn != null);
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			// get the user-specified connection or get a connection from the ResourceManager
			conn = isConnSupplied ? userConn : ResourceManager.getConnection();
		
			// construct the SQL statement
			final String SQL = sql;
		
		
			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL);
			}
		
			// prepare statement
			stmt = conn.prepareStatement( SQL );
			stmt.setMaxRows( maxRows );
		
			// bind parameters
			for (int i=0; sqlParams!=null && i<sqlParams.length; i++ ) {
				stmt.setObject( i+1, sqlParams[i] );
			}
		
		
			rs = stmt.executeQuery();
		
			// fetch the results
			return fetchMultiResults(rs);
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new TrensDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			if (!isConnSupplied) {
				ResourceManager.close(conn);
			}
		
		}
		
	}

	/** 
	 * Returns all rows from the trens table that match the specified arbitrary SQL statement
	 */
	public Trens[] findByDynamicWhere(String sql, Object[] sqlParams) throws TrensDaoException
	{
		// declare variables
		final boolean isConnSupplied = (userConn != null);
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			// get the user-specified connection or get a connection from the ResourceManager
			conn = isConnSupplied ? userConn : ResourceManager.getConnection();
		
			// construct the SQL statement
			final String SQL = SQL_SELECT + " WHERE " + sql;
		
		
			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL);
			}
		
			// prepare statement
			stmt = conn.prepareStatement( SQL );
			stmt.setMaxRows( maxRows );
		
			// bind parameters
			for (int i=0; sqlParams!=null && i<sqlParams.length; i++ ) {
				stmt.setObject( i+1, sqlParams[i] );
			}
		
		
			rs = stmt.executeQuery();
		
			// fetch the results
			return fetchMultiResults(rs);
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new TrensDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			if (!isConnSupplied) {
				ResourceManager.close(conn);
			}
		
		}
		
	}

}
